package com.book.dao.impl;

import com.book.dao.AdminDao;
import com.book.domain.Admin;
import com.book.domain.Book;
import com.book.utils.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminDaoImpl implements AdminDao {
    @Override
    public Admin login(String username, String password) {
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        Admin admin = null;
        String sql = "select * from admin where username=? and password=?";
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            pstat.setString(1,username);
            pstat.setString(2,password);
            rs = pstat.executeQuery();
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String tel = rs.getString("tel");
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                admin = new Admin(id, name, tel, password1, username1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.realeaseAll(rs, pstat, conn);
        }
        return admin;
    }

    @Override
    public List<Book> findBook() {
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> books = new ArrayList<>();
        String sql = "select * from book ";
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            rs = pstat.executeQuery();
            while (rs.next()) {
                int bookId = rs.getInt("bookId");
                String bookName = rs.getString("bookName");
                String bookAuthor = rs.getString("bookAuthor");
                String bookType = rs.getString("bookType");
                int amount = rs.getInt("amount");
                int number = rs.getInt("number");
                book = new Book(bookId, bookName, bookAuthor, bookType, amount, number);
                books.add(book);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.realeaseAll(rs, pstat, conn);
        }
        return books;
    }

    @Override
    public Book findBookById(String bookId) {
        int id = Integer.parseInt(bookId);
        Connection conn = null;
        PreparedStatement pstat = null;
        ResultSet rs = null;
        Book book = null;
        String sql = "select * from book where bookId=? ";
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            pstat.setInt(1,id);
            rs = pstat.executeQuery();
            while (rs.next()) {
                int bookId1 = rs.getInt("bookId");
                String bookName = rs.getString("bookName");
                String bookAuthor = rs.getString("bookAuthor");
                String bookType = rs.getString("bookType");
                int amount = rs.getInt("amount");
                int number = rs.getInt("number");
                book = new Book(bookId1, bookName, bookAuthor, bookType, amount, number);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.realeaseAll(rs, pstat, conn);
        }
        return book;
    }

    @Override
    public boolean updateBook(String bookId, String bookName, String bookAuthor, String bookType, int amount, int number) {
        int id = Integer.parseInt(bookId);
        Connection conn = null;
        PreparedStatement pstat = null;
        String sql = "update book set bookName=?,bookAuthor=?,bookType=?,amount=?,number=? where bookId=?";//表中有id和name这列
        int result = -1;
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            pstat.setString(1, bookName);
            pstat.setString(2, bookAuthor);
            pstat.setString(3, bookType);
            pstat.setInt(4, amount);
            pstat.setInt(5, number);
            pstat.setInt(6, id);
            result = pstat.executeUpdate();

        }catch (SQLException sqle) {
            sqle.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtil.realeaseAll(null,pstat, conn);
        }
        return result>0?true:false;
    }

    @Override
    public boolean addBook(String bookName, String bookAuthor, String bookType, int amount, int number) {
        Connection conn = null;
        PreparedStatement pstat = null;
        String sql = "insert into book(bookName,bookAuthor,bookType,amount,number) values (?,?,?,?,?)";//表中有id和name这列
        int result = -1;
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            pstat.setString(1, bookName);
            pstat.setString(2, bookAuthor);
            pstat.setString(3, bookType);
            pstat.setInt(4, amount);
            pstat.setInt(5, number);
            result = pstat.executeUpdate();

        }catch (SQLException sqle) {
            sqle.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtil.realeaseAll(null,pstat, conn);
        }
        return result>0?true:false;
    }

    @Override
    public boolean deleteBook(String bookId) {
        int id = Integer.parseInt(bookId);
        Connection conn = null;
        PreparedStatement pstat = null;
        String sql = "delete from book where bookId=?";//表中有id和name这列
        int result = -1;
        try {
            JDBCUtil.registerDriver();
            conn = JDBCUtil.getConnection();
            pstat = conn.prepareStatement(sql);
            pstat.setInt(1, id);
            result = pstat.executeUpdate();

        }catch (SQLException sqle) {
            sqle.printStackTrace();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            JDBCUtil.realeaseAll(null,pstat, conn);
        }
        return result>0?true:false;
    }
}
